﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using FreeOnlineApplication.Class.Entity;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Collections;
using System.Text;

namespace FreeOnlineApplication.Class.Repository
{
    public class UnitRepository
    {
        protected MySqlConnection m_connection;
        MySqlCommand aCommand = new MySqlCommand();
        UserRepository r_userRepository;

        public UnitRepository(MySqlConnection connection)
        {
            m_connection = connection;
            aCommand.Connection = m_connection;
            r_userRepository = new UserRepository(connection);
        }
        public void Insert(Unit unit)
        {
            string insertSQL = String.Format(@"insert into table_unit 
                (
                    unit_code,
                    unit_name,
                    unit_address,
                    unit_website,
                    unit_email) 
                VALUES ('{0}','{1}','{2}','{3}','{4}')",
                unit.CODE, unit.NAME, unit.ADDRESS, unit.WEBSITE, unit.EMAIL);
            aCommand.CommandText = insertSQL;
            aCommand.ExecuteNonQuery();
            unit.ID = GetMaximumId();
            //Create Administrator User
            User user = new User();
            user.CODE = "ADMIN";
            user.NAME = "Administrator";
            user.UNIT = unit;
            user.PASSWORD = RandomString(7, false); ;
            r_userRepository.Insert(user);
        }
        public User InsertReturnAdminUser(Unit unit)
        {
            unit.CODE = "HTL" + (GetMaximumId() + 1).ToString().PadLeft(5, '0');
            string insertSQL = String.Format(@"insert into table_unit 
                (
                    unit_code,
                    unit_name,
                    unit_address,
                    unit_website,
                    unit_email) 
                VALUES ('{0}','{1}','{2}','{3}','{4}')",
                unit.CODE, unit.NAME, unit.ADDRESS, unit.WEBSITE, unit.EMAIL);
            aCommand.CommandText = insertSQL;
            aCommand.ExecuteNonQuery();
            unit.ID = GetMaximumId();
            //Create Administrator User
            User user = new User();
            user.CODE = "ADMIN";
            user.NAME = "Administrator";
            user.UNIT = unit;
            user.PASSWORD = GetPassword(); 
            r_userRepository.Insert(user);
            return user;
        }
        public Unit GetById(int id)
        {
            string getByID = String.Format("select * from table_unit where unit_id = {0}", id);
            aCommand.CommandText = getByID;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            Unit unit = transform(aReader);
            aReader.Close();
            return unit;
        }
        public void Update(Unit unit)
        {
            string updateSQL = String.Format(@"update table_unit set 
                unit_code = '{0}', 
                unit_name='{1}',
                unit_address='{2}',
                unit_website='{3}',
                unit_email='{4}'
                where unit_id = {5}",
                unit.CODE, unit.NAME, unit.ADDRESS, unit.WEBSITE, unit.EMAIL, unit.ID);
            aCommand.CommandText = updateSQL;
            aCommand.ExecuteNonQuery();
        }
        public void Delete(Unit unit)
        {
            string deleteSQL = "delete from table_unit where unit_id = " + unit.ID;
            aCommand.CommandText = deleteSQL;
            aCommand.ExecuteNonQuery();
        }
        public int GetMaximumId()
        {
            string getByID = String.Format("SELECT max(unit_id) from table_unit");
            aCommand.CommandText = getByID;
            object r = aCommand.ExecuteScalar();
            int aReader = r is System.DBNull ? 0 : Convert.ToInt32(r);
            return aReader;
        }
        public Unit GetByCode(string code)
        {
            string getByID = String.Format("select * from table_unit where unit_code = '{0}'", code);
            aCommand.CommandText = getByID;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            Unit user = transform(aReader);
            aReader.Close();
            return user;
        }
        public Unit GetByEmail(string email)
        {
            string getByID = String.Format("select * from table_unit where unit_email = '{0}'", email);
            aCommand.CommandText = getByID;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            Unit user = transform(aReader);
            aReader.Close();
            return user;
        }
        private Unit transform(MySqlDataReader aReader)
        {
            Unit unit = null;
            while (aReader.Read())
            {
                unit = new Unit();
                unit.ID = Convert.ToInt32(aReader["unit_id"]);
                unit.CODE = aReader["unit_code"].ToString();
                unit.NAME = aReader["unit_name"].ToString();
                unit.ADDRESS = aReader["unit_address"].ToString();
                unit.WEBSITE = aReader["unit_website"].ToString();
                unit.EMAIL = aReader["unit_email"].ToString();
            }
            return unit;
        }
        private IList transformList(MySqlDataReader aReader)
        {
            IList result = new ArrayList();
            while (aReader.Read())
            {
                Unit unit = new Unit();
                unit.ID = Convert.ToInt32(aReader["unit_id"]);
                unit.CODE = aReader["unit_code"].ToString();
                unit.NAME = aReader["unit_name"].ToString();
                unit.ADDRESS = aReader["unit_address"].ToString();
                unit.WEBSITE = aReader["unit_website"].ToString();
                unit.EMAIL = aReader["unit_email"].ToString();
                result.Add(unit);
            }
            return result;
        }
        private string RandomString(int size, bool lowerCase)
        {
            StringBuilder builder = new StringBuilder();
            Random random = new Random();
            char ch;
            for (int i = 0; i < size; i++)
            {
                ch = Convert.ToChar(Convert.ToInt32(Math.Floor(26 * random.NextDouble() + 65)));
                builder.Append(ch);
            }
            if (lowerCase)
                return builder.ToString().ToLower();
            return builder.ToString();
        }
        private int RandomNumber(int min, int max)
        {
            Random random = new Random();
            return random.Next(min, max);
        }
        public string GetPassword()
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(RandomString(4, true));
            builder.Append(RandomNumber(1000, 9999));
            builder.Append(RandomString(2, false));
            return builder.ToString();
        } 
    }
}
